package com.kwum.toutiao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MySQLUtils {

    public static void main(String[] args) throws Exception{
        
        //获得数据库的连接
        Connection conn = getMySQLConnection("toutiao_article_data");
        //通过数据库的连接操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select username, password from account");//选择import java.sql.ResultSet;
        while(rs.next()){//如果对象中有数据，就会循环打印出来
            System.out.println(rs.getString("username")+","+rs.getString("password"));
        }
        
    }
    
    public static Connection getMySQLConnection(String databaseName) throws Exception{
        //声明Connection对象
        Connection con;
        //驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        //URL指向要访问的数据库名mydata
        String url = "jdbc:mysql://localhost:3306/" + databaseName + "?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false";
        //MySQL配置时的用户名
        String user = "root";
        //MySQL配置时的密码
        String password = "Kwum123";
        //加载驱动程序
        Class.forName(driver);
        //1.getConnection()方法，连接MySQL数据库！！
        con = DriverManager.getConnection(url,user,password);
        if(!con.isClosed()){
//            System.out.println("The database connection is complete!");
        }
        return con;
    }
    
    public static List<TouTiaoAccount> selectAccount() throws Exception{
    	List<TouTiaoAccount> accountList = new ArrayList<TouTiaoAccount>();
    	//获得数据库的连接
        Connection conn = getMySQLConnection("toutiao_article_data");
        //通过数据库的连接操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select phone, tuotiao_name, cookies_char from account");//选择import java.sql.ResultSet;
        while(rs.next()){//如果对象中有数据，就会循环打印出来
            accountList.add(new TouTiaoAccount(rs.getString("phone"), rs.getString("tuotiao_name"), rs.getString("cookies_char")));
        }
        return accountList;
    }
    
    public static void insertArticle(String username, String nickname, String pgc_id, String title, Integer is_recommend,
    		Integer impression_num, Integer play_num, String publish_time) throws Exception{
        //获得数据库的连接
        Connection conn = getMySQLConnection("toutiao_article_data");
        
        String sql="insert ignore into article (username, nickname, pgc_id, title, is_recommend, impression_num, play_num, publish_time) "
        		+ "values(?, ?, ?, ?, ?, ?, ?, ?)";
        //预编译sql语句
        PreparedStatement psmt = conn.prepareStatement(sql);
        //先对应SQL语句，给SQL语句传递参数
        psmt.setString(1, username);
        psmt.setString(2, nickname);
        psmt.setString(3, pgc_id);
        psmt.setString(4, title);
        psmt.setInt(5, is_recommend);
        psmt.setInt(6, impression_num);
        psmt.setInt(7, play_num);
        psmt.setString(8, publish_time);
        //执行SQL语句
        psmt.execute();
        conn.close();
    }
}
